package com.base.utils;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Table;
import org.apache.poi.hwpf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class PoiUtils {

    public enum FileType {
        XLS, XLSX, DOC, DOCX
    }

    // ======================== 核心导出方法 ========================
    public static void exportExcel(List<?> dataList, Class<?> clazz,
                                   HttpServletResponse response, String fileName,
                                   FileType fileType) {
        try (Workbook workbook = createWorkbook(dataList, clazz, fileType)) {
            setResponseHeader(response, fileName, getContentType(fileType));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new RuntimeException("Excel导出失败", e);
        }
    }

    public static void exportToDisk(List<?> dataList, Class<?> clazz,
                                    String filePath, FileType fileType) {
        try (Workbook workbook = createWorkbook(dataList, clazz, fileType);
             FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        } catch (Exception e) {
            throw new RuntimeException("导出到磁盘失败", e);
        }
    }

    // ======================== 模板导出 ========================
    public static void exportByTemplate(String templatePath, Map<String, Object> dataMap,
                                        HttpServletResponse response, String fileName,
                                        FileType fileType) {
        try (InputStream is = new FileInputStream(templatePath)) {
            if (fileType == FileType.XLS || fileType == FileType.XLSX) {
                Workbook workbook = WorkbookFactory.create(is);
                processExcelTemplate(workbook, dataMap);
                setResponseHeader(response, fileName, getContentType(fileType));
                workbook.write(response.getOutputStream());
                workbook.close();
            } else if (fileType == FileType.DOC || fileType == FileType.DOCX) {
                if (fileType == FileType.DOC) {
                    HWPFDocument doc = new HWPFDocument(is);
                    processDocTemplate(doc, dataMap);
                    setResponseHeader(response, fileName, getContentType(fileType));
                    doc.write(response.getOutputStream());
                    doc.close();
                } else {
                    XWPFDocument doc = new XWPFDocument(is);
                    processDocxTemplate(doc, dataMap);
                    setResponseHeader(response, fileName, getContentType(fileType));
                    doc.write(response.getOutputStream());
                    doc.close();
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("模板导出失败", e);
        }
    }

    // ======================== Excel 导入 ========================
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz) {
        try (InputStream is = file.getInputStream()) {
            String fileName = file.getOriginalFilename();
            FileType fileType = getFileType(fileName);

            Workbook workbook;
            if (fileType == FileType.XLS) {
                workbook = new HSSFWorkbook(is);
            } else {
                workbook = new XSSFWorkbook(is);
            }

            return parseExcel(workbook, clazz);
        } catch (Exception e) {
            throw new RuntimeException("Excel导入失败", e);
        }
    }

    // ======================== Word 导入 ========================
    public static Map<String, String> importWord(MultipartFile file) {
        try (InputStream is = file.getInputStream()) {
            String fileName = file.getOriginalFilename();
            FileType fileType = getFileType(fileName);

            if (fileType == FileType.DOC) {
                return parseDoc(new HWPFDocument(is));
            } else {
                return parseDocx(new XWPFDocument(is));
            }
        } catch (Exception e) {
            throw new RuntimeException("Word导入失败", e);
        }
    }

    // ======================== 私有方法 - Excel 处理 ========================
    private static Workbook createWorkbook(List<?> dataList, Class<?> clazz, FileType fileType) {
        Workbook workbook = fileType == FileType.XLS ?
                new HSSFWorkbook() : new SXSSFWorkbook(1000); // 支持大数据量

        Sheet sheet = workbook.createSheet("Sheet1");

        // 创建标题行
        Row headerRow = sheet.createRow(0);
        Field[] fields = clazz.getDeclaredFields();
        CellStyle headerStyle = createHeaderStyle(workbook);

        // 创建标题
        for (int i = 0; i < fields.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(fields[i].getName());
            cell.setCellStyle(headerStyle);
        }

        // 填充数据
        CellStyle dataStyle = createDataStyle(workbook);
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Object data = dataList.get(i);

            for (int j = 0; j < fields.length; j++) {
                try {
                    fields[j].setAccessible(true);
                    Object value = fields[j].get(data);
                    createCell(row, j, value, dataStyle);
                } catch (Exception e) {
                    throw new RuntimeException("字段访问错误", e);
                }
            }
        }

        // 自动调整列宽
        autoSizeColumns(sheet, fields.length);
        return workbook;
    }

    private static void processExcelTemplate(Workbook workbook, Map<String, Object> dataMap) {
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);

            // 处理普通单元格
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == CellType.STRING) {
                        String cellValue = cell.getStringCellValue();
                        cellValue = replacePlaceholders(cellValue, dataMap);
                        cell.setCellValue(cellValue);
                    }
                }
            }

            // 处理批注（作为另一种模板方式）
            if (workbook instanceof HSSFWorkbook) {
                processHSSFComments((HSSFWorkbook) workbook, sheet, dataMap);
            }
        }
    }

    // 修复：POI 5.x 中批注获取方式已改变
    private static void processHSSFComments(HSSFWorkbook workbook, Sheet sheet, Map<String, Object> dataMap) {
        if (sheet instanceof HSSFSheet) {
            HSSFSheet hssfSheet = (HSSFSheet) sheet;
            Map<CellAddress, ? extends Comment> comments = hssfSheet.getCellComments();

            for (Comment comment : comments.values()) {
                if (comment instanceof HSSFComment) {
                    HSSFComment hssfComment = (HSSFComment) comment;
                    String commentText = hssfComment.getString().getString();

                    Matcher matcher = Pattern.compile("\\$\\{(.+?)\\}").matcher(commentText);
                    if (matcher.find()) {
                        String key = matcher.group(1);
                        if (dataMap.containsKey(key)) {
                            int row = hssfComment.getRow();
                            int col = hssfComment.getColumn();
                            Row sheetRow = sheet.getRow(row);
                            if (sheetRow == null) sheetRow = sheet.createRow(row);
                            Cell cell = sheetRow.getCell(col);
                            if (cell == null) cell = sheetRow.createCell(col);
                            setCellValue(cell, dataMap.get(key));
                        }
                    }
                }
            }
        }
    }

    private static <T> List<T> parseExcel(Workbook workbook, Class<T> clazz) throws Exception {
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rows = sheet.iterator();
        if (!rows.hasNext()) return Collections.emptyList();

        // 获取标题行
        Row headerRow = rows.next();
        List<String> headers = new ArrayList<>();
        for (Cell cell : headerRow) {
            headers.add(cell.getStringCellValue());
        }

        // 解析数据行
        List<T> result = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();

        while (rows.hasNext()) {
            Row row = rows.next();
            T obj = clazz.getDeclaredConstructor().newInstance();

            for (int i = 0; i < headers.size(); i++) {
                Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                String fieldName = headers.get(i);

                for (Field field : fields) {
                    if (field.getName().equals(fieldName)) {
                        field.setAccessible(true);
                        setFieldValue(obj, field, cell);
                        break;
                    }
                }
            }
            result.add(obj);
        }
        return result;
    }

    // ======================== 私有方法 - Word 处理 ========================
    private static void processDocTemplate(HWPFDocument doc, Map<String, Object> dataMap) {
        Range range = doc.getRange();

        // 替换段落中的占位符
        for (int i = 0; i < range.numParagraphs(); i++) {
            Paragraph paragraph = range.getParagraph(i);
            String text = paragraph.text();
            text = replacePlaceholders(text, dataMap);
            paragraph.replaceText(paragraph.text(), text);
        }

        // 替换表格中的占位符
        TableIterator tableIter = new TableIterator(range);
        while (tableIter.hasNext()) {
            Table table = tableIter.next();
            for (int rowIdx = 0; rowIdx < table.numRows(); rowIdx++) {
                TableRow row = table.getRow(rowIdx);
                for (int colIdx = 0; colIdx < row.numCells(); colIdx++) {
                    TableCell cell = row.getCell(colIdx);
                    String text = cell.text().trim();
                    text = replacePlaceholders(text, dataMap);
                    cell.replaceText(cell.text(), text);
                }
            }
        }
    }

    private static void processDocxTemplate(XWPFDocument doc, Map<String, Object> dataMap) {
        // 处理段落
        for (XWPFParagraph p : doc.getParagraphs()) {
            for (XWPFRun run : p.getRuns()) {
                String text = run.getText(0);
                if (text != null) {
                    text = replacePlaceholders(text, dataMap);
                    run.setText(text, 0);
                }
            }
        }

        // 处理表格
        for (XWPFTable tbl : doc.getTables()) {
            for (XWPFTableRow row : tbl.getRows()) {
                for (XWPFTableCell cell : row.getTableCells()) {
                    for (XWPFParagraph p : cell.getParagraphs()) {
                        for (XWPFRun run : p.getRuns()) {
                            String text = run.getText(0);
                            if (text != null) {
                                text = replacePlaceholders(text, dataMap);
                                run.setText(text, 0);
                            }
                        }
                    }
                }
            }
        }

        // 处理页眉
        for (XWPFHeader header : doc.getHeaderList()) {
            for (XWPFParagraph p : header.getParagraphs()) {
                for (XWPFRun run : p.getRuns()) {
                    String text = run.getText(0);
                    if (text != null) {
                        text = replacePlaceholders(text, dataMap);
                        run.setText(text, 0);
                    }
                }
            }
        }

        // 处理页脚
        for (XWPFFooter footer : doc.getFooterList()) {
            for (XWPFParagraph p : footer.getParagraphs()) {
                for (XWPFRun run : p.getRuns()) {
                    String text = run.getText(0);
                    if (text != null) {
                        text = replacePlaceholders(text, dataMap);
                        run.setText(text, 0);
                    }
                }
            }
        }
    }

    private static Map<String, String> parseDoc(HWPFDocument doc) {
        Map<String, String> content = new HashMap<>();

        // 读取文本内容
        Range range = doc.getRange();
        content.put("text", range.text());

        // 读取表格内容
        TableIterator tableIter = new TableIterator(range);
        int tableIndex = 0;
        while (tableIter.hasNext()) {
            Table table = tableIter.next();
            StringBuilder tableContent = new StringBuilder();
            for (int rowIdx = 0; rowIdx < table.numRows(); rowIdx++) {
                TableRow row = table.getRow(rowIdx);
                for (int colIdx = 0; colIdx < row.numCells(); colIdx++) {
                    TableCell cell = row.getCell(colIdx);
                    tableContent.append(cell.text().trim()).append("\t");
                }
                tableContent.append("\n");
            }
            content.put("table_" + (++tableIndex), tableContent.toString());
        }

        return content;
    }

    private static Map<String, String> parseDocx(XWPFDocument doc) {
        Map<String, String> content = new HashMap<>();

        // 读取段落文本
        StringBuilder textContent = new StringBuilder();
        for (XWPFParagraph p : doc.getParagraphs()) {
            textContent.append(p.getText()).append("\n");
        }
        content.put("text", textContent.toString());

        // 读取表格内容
        int tableIndex = 0;
        for (XWPFTable table : doc.getTables()) {
            StringBuilder tableContent = new StringBuilder();
            for (XWPFTableRow row : table.getRows()) {
                for (XWPFTableCell cell : row.getTableCells()) {
                    tableContent.append(cell.getText()).append("\t");
                }
                tableContent.append("\n");
            }
            content.put("table_" + (++tableIndex), tableContent.toString());
        }

        return content;
    }

    // ======================== 高级功能 ========================
    // 1. 插入图片到Excel
    public static void addImageToExcel(Workbook workbook, Sheet sheet,
                                       byte[] imageData, int rowIdx, int colIdx,
                                       int width, int height) {
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        CreationHelper helper = workbook.getCreationHelper();

        ClientAnchor anchor;
        if (workbook instanceof HSSFWorkbook) {
            anchor = new HSSFClientAnchor(0, 0, 0, 0,
                    (short) colIdx, rowIdx, (short) (colIdx + 1), rowIdx + 1);
            ((HSSFClientAnchor) anchor).setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        } else {
            anchor = helper.createClientAnchor();
            anchor.setCol1(colIdx);
            anchor.setRow1(rowIdx);
            anchor.setCol2(colIdx + width);
            anchor.setRow2(rowIdx + height);
        }

        int pictureType = getPictureType(imageData);
        int pictureIdx = workbook.addPicture(imageData, pictureType);
        drawing.createPicture(anchor, pictureIdx);
    }

    // 2. 插入图片到Word
    public static void addImageToWord(XWPFDocument doc, byte[] imageData,
                                      int width, int height, String filename) {
        XWPFParagraph paragraph = doc.createParagraph();
        XWPFRun run = paragraph.createRun();
        try {
            run.addPicture(new ByteArrayInputStream(imageData),
                    getPictureType(imageData), filename,
                    Units.toEMU(width), Units.toEMU(height));
        } catch (Exception e) {
            throw new RuntimeException("添加图片到Word失败", e);
        }
    }

    // 3. POI 5.x 中图表API已移除，建议使用其他图表库
    // 此方法已删除，因为POI 5.x不再支持内置图表API

    // 4. 设置单元格公式
    public static void setCellFormula(Cell cell, String formula) {
        cell.setCellFormula(formula);
        if (cell.getSheet().getWorkbook() instanceof HSSFWorkbook) {
            // 强制重新计算旧格式工作簿
            cell.getSheet().getWorkbook().getCreationHelper()
                    .createFormulaEvaluator().evaluateFormulaCell(cell);
        }
    }

    // ======================== 辅助方法 ========================
    private static String replacePlaceholders(String text, Map<String, Object> dataMap) {
        Matcher matcher = Pattern.compile("\\$\\{(.+?)\\}").matcher(text);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            String key = matcher.group(1);
            if (dataMap.containsKey(key)) {
                matcher.appendReplacement(sb, String.valueOf(dataMap.get(key)));
            }
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    private static void setFieldValue(Object obj, Field field, Cell cell) throws Exception {
        Class<?> fieldType = field.getType();

        if (fieldType == String.class) {
            field.set(obj, getCellStringValue(cell));
        } else if (fieldType == int.class || fieldType == Integer.class) {
            field.set(obj, (int) cell.getNumericCellValue());
        } else if (fieldType == double.class || fieldType == Double.class) {
            field.set(obj, cell.getNumericCellValue());
        } else if (fieldType == BigDecimal.class) {
            field.set(obj, BigDecimal.valueOf(cell.getNumericCellValue()));
        } else if (fieldType == Date.class) {
            field.set(obj, cell.getDateCellValue());
        } else if (fieldType == boolean.class || fieldType == Boolean.class) {
            field.set(obj, cell.getBooleanCellValue());
        } else {
            field.set(obj, getCellStringValue(cell));
        }
    }

    private static String getCellStringValue(Cell cell) {
        if (cell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.FORMULA) {
            return cell.getCellFormula();
        } else {
            return cell.getStringCellValue();
        }
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value == null) return;

        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    private static int getPictureType(byte[] imageData) {
        // 更健壮的图片类型检测
        if (imageData.length < 12) return Workbook.PICTURE_TYPE_JPEG;

        // PNG: 89 50 4E 47 0D 0A 1A 0A
        if (imageData[1] == 'P' && imageData[2] == 'N' && imageData[3] == 'G')
            return Workbook.PICTURE_TYPE_PNG;

        // GIF: 47 49 46 38
        // if (imageData[0] == 'G' && imageData[1] == 'I' && imageData[2] == 'F')
        //     return Workbook.PICTURE_TYPE_GIF;

        // JPEG: FF D8 FF
        if ((imageData[0] & 0xFF) == 0xFF && (imageData[1] & 0xFF) == 0xD8)
            return Workbook.PICTURE_TYPE_JPEG;

        // BMP: 42 4D
        if (imageData[0] == 'B' && imageData[1] == 'M')
            return Workbook.PICTURE_TYPE_DIB;

        return Workbook.PICTURE_TYPE_JPEG; // 默认JPEG
    }

    private static FileType getFileType(String fileName) {
        if (fileName == null) throw new IllegalArgumentException("文件名不能为空");

        String ext = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
        switch (ext) {
            case "xls": return FileType.XLS;
            case "xlsx": return FileType.XLSX;
            case "doc": return FileType.DOC;
            case "docx": return FileType.DOCX;
            default: throw new IllegalArgumentException("不支持的文件类型: " + ext);
        }
    }

    private static String getContentType(FileType fileType) {
        switch (fileType) {
            case XLS: return "application/vnd.ms-excel";
            case XLSX: return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            case DOC: return "application/msword";
            case DOCX: return "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
            default: return "application/octet-stream";
        }
    }

    // ======================== 样式相关方法 ========================
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);

        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);

        return style;
    }

    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        return style;
    }

    private static void autoSizeColumns(Sheet sheet, int columnCount) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
            // 额外宽度补偿
            int currentWidth = sheet.getColumnWidth(i);
            sheet.setColumnWidth(i, currentWidth + 1024);
        }
    }

    private static void setResponseHeader(HttpServletResponse response, String fileName, String contentType) {
        response.setContentType(contentType);
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" +
                new String(fileName.getBytes(), java.nio.charset.StandardCharsets.ISO_8859_1));
    }

    private static void createCell(Row row, int index, Object value, CellStyle style) {
        Cell cell = row.createCell(index);
        cell.setCellStyle(style);

        if (value == null) return;

        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format((Date) value));
        } else {
            cell.setCellValue(value.toString());
        }
    }

    public static void main(String[] args) {
    /*    // 导出为旧版Excel
        PoiUtils.exportExcel(users, User.class, response, "users.xls", FileType.XLS);

        // 导出为新版Excel（大数据量优化）
        PoiUtils.exportExcel(largeData, Report.class, response, "report.xlsx", FileType.XLSX);

        // 导出到磁盘
        PoiUtils.exportToDisk(products, Product.class, "/data/products.xlsx", FileType.XLSX);

        // 导出为旧版Word
        Map<String, Object> data = new HashMap<>();
        data.put("title", "合同书");
        data.put("content", "这是合同正文内容...");
        PoiUtils.exportByTemplate("/templates/contract.doc", data,
                response, "contract.doc", FileType.DOC);

        // 导出为新版Word（带图片）
        XWPFDocument doc = new XWPFDocument();
        // ...构建文档内容...
        byte[] image = Files.readAllBytes(Paths.get("/images/logo.png"));
        PoiUtils.addImageToWord(doc, image, 100, 50, "company_logo");

        // 创建带图表的工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("销售报表");

        // 添加数据
        // ...

        // 创建图表
        Map<String, Double> salesData = new LinkedHashMap<>();
        salesData.put("1月", 12000.0);
        salesData.put("2月", 18000.0);
        salesData.put("3月", 15000.0);
        PoiUtils.createChart(workbook, sheet, 5, 0, "季度销售趋势", salesData);

        // 添加图片
        byte[] productImage = getProductImage();
        PoiUtils.addImageToExcel(workbook, sheet, productImage, 0, 5, 200, 100);

        // 导出
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());


        // 导入Excel
        @PostMapping("/import")
        public Result importData(@RequestParam MultipartFile file) {
            if (file.getOriginalFilename().endsWith(".xls") ||
                    file.getOriginalFilename().endsWith(".xlsx")) {
                List<Product> products = PoiUtils.importExcel(file, Product.class);
                // 处理数据...
            } else if (file.getOriginalFilename().endsWith(".doc") ||
                    file.getOriginalFilename().endsWith(".docx")) {
                Map<String, String> content = PoiUtils.importWord(file);
                // 解析Word内容...
            }
            return Result.success();
        }*/
    }
}